Function GetIPAddress()
    Const strComputer As String = "."   ' Computer name. Dot means local computer
    Dim objWMIService, IPConfigSet, IPConfig, IPAddress, i
    Dim strIPAddress As String

    ' Connect to the WMI service
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    ' Get all TCP/IP-enabled network adapters
    Set IPConfigSet = objWMIService.ExecQuery _
        ("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")

    ' Get all IP addresses associated with these adapters
    For Each IPConfig In IPConfigSet
        IPAddress = IPConfig.IPAddress
        If Not IsNull(IPAddress) Then
            strIPAddress = strIPAddress & Join(IPAddress, ", ")
        End If
    Next

    GetIPAddress = strIPAddress
End Function


Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean

IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function




Sub t()
 Dim cn As ADODB.Connection
    Dim str As String
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=10.7.21.57;" & "DATABASE=worklog;" & "UID=zkr;PWD=zkr@ncl;OPTION=3"
    
    cn.Open
    
    If (cn.Execute("select count(*) from submitRecord where ipAddress = '" & GetIPAddress & "' and submitTime = '" & Date & "'").Fields.Item(0) >= 3) Then
        MsgBox "同一ip地址在一天内只能插入三次！日志导入失败"
        Exit Sub
    End If
    
    'Open "C:\tttt.txt" For Output As #1
  
    Set xlsheet = ThisWorkbook.ActiveSheet
    
    
    
    
  ' Automatically get the variable of start rows
  'MsgBox xlsheet.Cells(1, 1)
  
  
  For jj = 1 To ActiveSheet.UsedRange.Rows.Count
    If xlsheet.Cells(jj, 2).Value = "填报人" Then
        startrow = jj + 1
    End If
  
  Next
  
  

  '前四项必须填写'
  
  For i = startrow To ActiveSheet.UsedRange.Rows.Count
  
    For k = 1 To 4
        If (xlsheet.Cells(i, k).Value = "") Then
            MsgBox "第 " & i & " 行前四项必须填写"
            MsgBox "日志导入失败"
        End If
    Next
  Next
  
  '校验工作日志日期合法性
  For i = startrow To ActiveSheet.UsedRange.Rows.Count
  
        
       ' On Error GoTo mywrong
        
        temp = CDate(xlsheet.Cells(i, 3).Value)
        
        temp = CDate(xlsheet.Cells(i, 7).Value)
        
        temp = CDate(xlsheet.Cells(i, 8).Value)
    
  Next
  
' 日期格式错误处理
mywrong:
     If (Err.Number) Then
         MsgBox "日期格式不对，请检查第 " & i & " 行日期格式"
         MsgBox "日志导入失败"
         Exit Sub
     End If
  '检查百分比进度合法性
  
  For i = startrow To ActiveSheet.UsedRange.Rows.Count
    If xlsheet.Cells(i, 10).Value > 1 Then
        MsgBox "第 " & i & " 行进度百分比不能大于1"
        Exit Sub
    End If
  Next
  
  
  
  '工作类别校验'
  worktype = Array("综合事务", "项目管理", "评审", "发布相关", "设计", "开发编码", "单元测试", "学习培训", "会议", "运维", "配合业务验收", "配合技术测试", "配合联调测试", "临时任务", "其他 ", "请假", "倒休")

  
  For i = startrow To ActiveSheet.UsedRange.Rows.Count
    If Not IsInArray(xlsheet.Cells(i, 4).Value, worktype) Then
        MsgBox "第 " & i & " 行工作类别错误"
        Exit Sub
    End If
  Next
  
  
 ' MsgBox xlsheet.Cells(startrow, 3).Value
   thismonth = xlsheet.Cells(startrow, 3).Value
 
monthstring = Year(DateValue(thismonth)) & "-" & Month(DateValue(thismonth))
  
 a = "delete from worklog_zkr where name like '%" & xlsheet.Cells(startrow, 2) & "%' and date_format(time,'%Y-%m') = date_format(str_to_date('" & monthstring & "','%Y-%m'),'%Y-%m')"

 cn.Execute a
 
 
  '要求开发人员必须填写工作计划
  If (xlsheet.Cells(3, 4) = "开发") And (xlsheet.Cells(3, 8) = "" Or xlsheet.Cells(3, 9) = "" Or xlsheet.Cells(3, 10) = "") Then
    MsgBox "要求开发人员必须填写工作计划,日志导入失败"
    Exit Sub
  End If
  
  
 'MsgBox a
 'Print #1, a
' cn.Execute a
  
  personId = "0728"
  'loop the sheet to get all worklog into sql clause
  For i = startrow To ActiveSheet.UsedRange.Rows.Count
    
    
    
    fsql = ""
    
    
    tsql = "insert into worklog_zkr(personId,companyName,name,time,workType,workDetail,requirements,startDate,endDate,daysNeed,progressPercentage,hoursToday,overworkHours,note) values "
    
    tsql = tsql & " ('" & personId & "',"

    flag = True
    
    ' a for loop to get all the sqls according to excel data
    
    For j = 1 To 13
        If xlsheet.Cells(i, j).Value = "" Then
            If j = 13 Then
                tsql = tsql & "null"
            Else
                tsql = tsql & "null,"
            End If
        Else
            If j = 13 Then
                tsql = tsql & "'" & Replace(Trim(xlsheet.Cells(i, j).Value), "'", "\'") & "'"
            Else
                tsql = tsql & "'" & Replace(Trim(xlsheet.Cells(i, j).Value), "'", "\'") & "',"
            End If
        End If
    
    Next
    
    tsql = tsql & ")"
    'MsgBox tsql
    'Print #1, tsql
     
     On Error GoTo sqlError
    ' MsgBox tsql
    cn.Execute tsql
     
  Next

'Sql语句错误处理
sqlError:
    
    If (Err.Number) Then
             MsgBox "导入 Sql 语句错误，当前Sql语句为: 具体错误信息是:" & Err.Description
             MsgBox "日志导入失败"
             Exit Sub
    End If
cn.Execute "insert into submitRecord (submitTime,ipAddress) values('" & Date & "','" & GetIPAddress & "')"

 
 MsgBox "日志导入完成当前页"
 
 'Close #1
 End Sub

